package repository

import (
	"gorm.io/gorm"
	"shop-bean/model"
	"shop-bean/vo"
)

type IShippingTemplatesRegionRepository interface {
	BatchDelete(db *gorm.DB, tempIds []uint) error
	FindGroup(db *gorm.DB, tempId uint) ([]*vo.ShippingTemplatesRegionVO, error)
}

type ShippingTemplatesRegionRepository struct {
}

func NewShippingTemplatesRegionRepository() IShippingTemplatesRegionRepository {
	return &ShippingTemplatesRegionRepository{}
}

func (r *ShippingTemplatesRegionRepository) BatchDelete(db *gorm.DB, tempIds []uint) error {
	return db.Where("temp_id IN ?", tempIds).Where("status = 1").Delete(&model.ShippingTemplatesRegion{}).Error
}

func (r *ShippingTemplatesRegionRepository) FindGroup(db *gorm.DB, tempId uint) (rows []*vo.ShippingTemplatesRegionVO, err error) {
	return rows, db.Raw(`
		SELECT
			g.uniq_id,
			g.province_id,
			g.city_id_str,
			sys_city. NAME name_str,
			g.first,
			g.first_price,
			g.renewal,
			g.renewal_price
		FROM
			(
				SELECT
					t.uniq_id,
					GROUP_CONCAT(DISTINCT t.city_id) AS city_id_str,
					t.province_id,
					t.first,
					t.first_price,
					t.renewal,
					t.renewal_price
				FROM
					(
						SELECT
							province_id,
							city_id,
							uniq_id,
							first,
							first_price,
							renewal,
							renewal_price
						FROM
							tz_shipping_templates_region
						WHERE
							temp_id = ?
					) t
				GROUP BY
					t.uniq_id,
					t.province_id,
					t.first,
					t.first_price,
					t.renewal,
					t.renewal_price
			) g
		LEFT JOIN tz_sys_city sys_city ON g.province_id = sys_city.city_id
		ORDER BY g.province_id
	`, tempId).Find(&rows).Error
}
